using System;
using System.Collections.Generic;
using System.Text;
using SqlTools.DataAccessLibrary;
using System.Data.SqlClient;
using System.Data;

namespace SqlTools.SampleDAL.Data
{
	public class DbAddress : DbBase<AddressPrimaryKey, Address>
	{
		public DbAddress()
			: base("daltest")
		{
		}

		public DbAddress(string connectionStringName)
			: base(connectionStringName)
		{
		}

		#region Select
		public override Address SelectByPK(AddressPrimaryKey key)
		{
			Address address = null;
			using ( SqlConnection conn = new SqlConnection(_connectionString) )
			{
				SqlCommand cmd = new SqlCommand("daltest_Address_SelectByPK", conn);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@AddressID", SqlDbType.Int).Value = key.AddressID;

				conn.Open();

				SqlDataReader sdr = cmd.ExecuteReader();

				if ( sdr.Read() )
				{
					address = Address.CreateFromReader(sdr);
					address._ConnectionStringName = _connectionStringName;
				}
				sdr.Close();

				conn.Close();
			}

			return address;
		}

		public DbList<Address> SelectByCustomerID(int customerID)
		{
			DbList<Address> addresses = new DbList<Address>();
			using ( SqlConnection conn = new SqlConnection(_connectionString) )
			{
				SqlCommand cmd = new SqlCommand("daltest_Address_SelectByCustomerID", conn);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerID;

				conn.Open();

				SqlDataReader sdr = cmd.ExecuteReader();

				while ( sdr.Read() )
				{
					Address address = Address.CreateFromReader(sdr);
					address._ConnectionStringName = _connectionStringName;
					addresses.Add(address);
				}
				sdr.Close();

				conn.Close();
			}

			return addresses;
		}
		#endregion

		#region Insert
		public override AddressPrimaryKey Insert(Address address, SqlConnection conn, SqlTransaction trans)
		{
			address._ConnectionStringName = _connectionStringName;

			bool destroyConn = false;

			if ( conn == null )
			{
				destroyConn = true;
				conn = new SqlConnection(_connectionString);
			}

			try
			{
				SqlCommand cmd = new SqlCommand("daltest_Address_Insert", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = trans;

				if ( conn.State != ConnectionState.Open )
				{
					conn.Open();
				}

				cmd.Parameters.Add("@AddressID", SqlDbType.Int).Direction = ParameterDirection.Output;
				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = address.CustomerID;
				cmd.Parameters.Add("@Line1", SqlDbType.NVarChar, 100).Value = address.Line1;
				cmd.Parameters.Add("@Line2", SqlDbType.NVarChar, 100).Value = address.Line2;
				cmd.Parameters.Add("@Line3", SqlDbType.NVarChar, 100).Value = address.Line3;
				cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = address.City;
				cmd.Parameters.Add("@State", SqlDbType.NVarChar, 50).Value = address.State;
				cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 50).Value = address.PostalCode;

				cmd.ExecuteNonQuery();

				if ( cmd.Parameters["@AddressID"].Value != DBNull.Value )
				{
					address.Key.AddressID = (int)cmd.Parameters["@AddressID"].Value;
				}
				address._UpdateState(ObjectState.None);
			}
			finally
			{
				if ( destroyConn && conn.State == ConnectionState.Open )
				{
					conn.Close();
				}

				if ( destroyConn )
				{
					conn.Dispose();
				}
			}

			return address.Key;
		}
		#endregion

		#region Update
		public override int Update(Address address, SqlConnection conn, SqlTransaction trans)
		{
			address._ConnectionStringName = _connectionStringName;

			bool destroyConn = false;

			if ( conn == null )
			{
				destroyConn = true;
				conn = new SqlConnection(_connectionString);
			}

			try
			{
				SqlCommand cmd = new SqlCommand("daltest_Address_Update", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = trans;

				if ( conn.State != ConnectionState.Open )
				{
					conn.Open();
				}

				cmd.Parameters.Add("@AddressID", SqlDbType.Int).Value = address.Key.AddressID;
				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = address.CustomerID;
				cmd.Parameters.Add("@Line1", SqlDbType.NVarChar, 100).Value = address.Line1;
				cmd.Parameters.Add("@Line2", SqlDbType.NVarChar, 100).Value = address.Line2;
				cmd.Parameters.Add("@Line3", SqlDbType.NVarChar, 100).Value = address.Line3;
				cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = address.City;
				cmd.Parameters.Add("@State", SqlDbType.NVarChar, 50).Value = address.State;
				cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 50).Value = address.PostalCode;

				int rows = cmd.ExecuteNonQuery();

				address._UpdateState(ObjectState.None);

				return rows;
			}
			finally
			{
				if ( destroyConn && conn.State == ConnectionState.Open )
				{
					conn.Close();
				}

				if ( destroyConn )
				{
					conn.Dispose();
				}
			}
		}
		#endregion

		#region Delete
		public override int Delete(Address address, SqlConnection conn, SqlTransaction trans)
		{
			bool destroyConn = false;

			if ( conn == null )
			{
				destroyConn = true;
				conn = new SqlConnection(_connectionString);
			}

			try
			{
				SqlCommand cmd = new SqlCommand("daltest_Address_Delete", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = trans;

				if ( conn.State != ConnectionState.Open )
				{
					conn.Open();
				}

				cmd.Parameters.Add("@AddressID", SqlDbType.Int).Value = address.Key.AddressID;

				int rows = cmd.ExecuteNonQuery();

				address._UpdateState(ObjectState.Deleted);

				return rows;
			}
			finally
			{
				if ( destroyConn && conn.State == ConnectionState.Open )
				{
					conn.Close();
				}

				if ( destroyConn )
				{
					conn.Dispose();
				}
			}
		}
		#endregion
	}
}
